package oop.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import oop.db.Database;
import oop.db.dao.SectionDAO;

public class Test {

	private int id;
	private String name;
	private String type;
	private String description;
	private SimpleUser author;
	private Date createDate;
	private SimpleCategory category;
	private int time;
	private List<Question> questions = null;
	private List<Question> immutableQuestions = null;
	private List<Section> sections = null;
	private List<Section> immutableSections = null;

	public static List<Test> fetchById(int id) throws SQLException {
		String sql = "SELECT test_id, test_name, test_description, "
				+ "test_create_date, test_category, cat_name, "
				+ "test_author, user_name, test_type, test_time "
				+ "FROM tblTest, tblUser, tblCategory "
				+ "WHERE test_author = user_id AND test_category = cat_id " 
				+ "AND test_id = " + id;
		return fetch(sql);
	}
	
	public static int count() throws SQLException {
		String sql = "SELECT COUNT(*) FROM tblTest";
		return Database.get().count(sql);
	}
	
	public static List<Test> fetch(int start, int length) throws SQLException {
		String sql = "SELECT test_id, test_name, test_description, "
				+ "test_create_date, test_category, cat_name, "
				+ "test_author, user_name, test_type, test_time "
				+ "FROM tblTest, tblUser, tblCategory "
				+ "WHERE test_author = user_id AND test_category = cat_id";
		return fetch(sql);
	}

	public static int countByStudent(int id) throws SQLException {
		String sql = "SELECT COUNT(*) " +
				"FROM tblTest, tblUserTest " +
				"WHERE tblTest.test_id = tblUserTest.test_id";
		return Database.get().count(sql);
	}
	
	public static List<Test> fetchByStudent(int id, int start, int length) throws SQLException {
		String sql = "SELECT test_id, test_name, test_description, "
				+ "test_create_date, test_category, cat_name, "
				+ "test_author, user_name, test_type, test_time "
				+ "FROM  tblTest, tblUserTest, tblCategory " 
				+ "WHERE test_author = tblUser.user_id AND test_category = cat_id" 
				+ " AND tblUser.user_id = " + id
				+ " tblTest.test_id = tblUserTest.test_id "
				+ "LIMIT " + start + ", " + length;
		return fetch(sql);
	}
	
	public static int countByCategory(int id) throws SQLException {
		String sql = "SELECT COUNT(*) " +
				"FROM tblTest " +
				"WHERE test_category = " + id;
		return Database.get().count(sql);
	}

	public static List<Test> fetchByCategory(int id, int start, int length) throws SQLException {
		String sql = "SELECT test_id, test_name, test_description, "
				+ "test_create_date, test_category, cat_name, "
				+ "test_author, user_name, test_type, test_time "
				+ "FROM  tblTest, tblUser, tblCategory " 
				+ "WHERE test_author = user_id AND test_category = cat_id "
				+ "AND cat_id = " + id + " "
				+ "LIMIT " + start + ", " + length;
		return fetch(sql);
	}
	
	public static int countByAuthor(int id) throws SQLException {
		String sql = "SELECT COUNT(*) " +
				"FROM tblTest " +
				"WHERE test_author= " + id;
		return Database.get().count(sql);
	}

	public static List<Test> fetchByAuthor(int id, int start, int length) throws SQLException {
		String sql = "SELECT test_id, test_name, test_description, "
				+ "test_create_date, test_category, cat_name, "
				+ "test_author, user_name, test_type, test_time "
				+ "FROM  tblTest, tblUser, tblCategory " 
				+ "WHERE test_author = user_id AND test_category = cat_id "
				+ "AND test_author = " + id + " "
				+ "LIMIT " + start + ", " + length;
		return fetch(sql);
	}

	private static List<Test> fetch(String sql) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
	
			List<Test> tests = new ArrayList<Test>();
			while (rs.next()) {
				Test test = new Test(rs.getInt(1), rs.getString(2), rs.getString(3),
						rs.getDate(4), new SimpleCategory(rs.getInt(5), rs.getString(6)),
						new SimpleUser(rs.getInt(7), rs.getString(8)), rs.getString(9),
						rs.getInt(10));
				tests.add(test);
			}
			return tests;
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public void persist() throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			conn = Database.get().getConnection();
			if (id > 0) {
				String sql = "UPDATE tblTest " +
						"SET test_name=?, test_description=?" +
						", test_type=?, test_time=? " +
						"WHERE test_id=?";
				stmt = conn.prepareStatement(sql);
				stmt.setString(1, name);
				stmt.setString(2, description);
				stmt.setString(3, type);
				stmt.setInt(4, id);
				stmt.setInt(5, time);
				stmt.executeUpdate(sql);
			} else {
				String sql = "INSERT INTO tblTest (test_name, test_description, " +
						"test_create_date, test_author, test_category, test_type, test_time) " +
						"VALUES (?, ?, ?, ?, ?, ?, ?)";
				stmt = conn.prepareStatement(sql);
				stmt.setString(1, name);
				stmt.setString(2, description);
				stmt.setDate(3, new java.sql.Date(new Date().getTime()));
				stmt.setInt(4, author.getId());
				stmt.setInt(5, category.getId());
				stmt.setString(6, type);
				stmt.setInt(7, time);
				stmt.executeUpdate();
				
				ResultSet rs = stmt.getGeneratedKeys();
				rs.next();
				id = rs.getInt(1);
			}
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static int drop(int id) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		try {
			String sql = "DELETE FROM tblTest WHERE test_id = " + id;
			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			return stmt.executeUpdate(sql);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public Test(String name, String description, SimpleCategory category,
			SimpleUser author, String type, int time) {
		this(0, name, description, null, category, author, type, time);
	}

	public Test(int id, String name, String description, Date createDate,
			SimpleCategory category, SimpleUser author, String type, int time) {
		super();
		this.id = id;
		this.name = name;
		this.description = description;
		this.createDate = createDate;
		this.category = category;
		this.author = author;
		this.type = type;
		this.time = time;
	}

	public Collection<Question> getQuestions() throws SQLException {
		if (questions == null) {
			questions = new ArrayList<Question>();
			for (Section section : getSections()) {
				questions.addAll(section.getQuestions());
			}
			immutableQuestions = Collections.unmodifiableList(questions);
		}
		return immutableQuestions;
	}
	
	public List<Section> getSections() {
		if (sections == null) {
			try {
				sections = SectionDAO.fetchByTest(this);
				immutableSections = Collections.unmodifiableList(sections);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return immutableSections;
	}
	
	public void addSection(Section s) {
		if (s.getTestId() > 0) {
			throw new IllegalArgumentException("Already in another test");
		}
		sections.add(s);
		s.setTestId(id);
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getDescription() {
		return description;
	}

	public void setDescription(String descpription) {
		this.description = descpription;
	}

	public Date getCreateDate() {
		return createDate;
	}

	public int getId() {
		return id;
	}

	public void setCategory(SimpleCategory category) {
		this.category = category;
	}

	public SimpleCategory getCategory() {
		return category;
	}

	public void setAuthor(SimpleUser author) {
		this.author = author;
	}

	public SimpleUser getAuthor() {
		return author;
	}

	public void setType(String type) {
		this.type = type;
	}

	public String getType() {
		return type;
	}

	public void setTime(int time) {
		this.time = time;
	}

	public int getTime() {
		return time;
	}
	
}
